IF OBJECT_ID('ssf.gbl_acquire_semaphore_connection') IS NOT NULL
	DROP PROCEDURE ssf.gbl_acquire_semaphore_connection
GO

CREATE PROCEDURE ssf.gbl_acquire_semaphore_connection
	@semaphore_name NVARCHAR(128)
	, @connections_to_acquire INT = 1
	, @requested_by_user NVARCHAR(128) = NULL
	, @block_fl BIT = 1
	, @poll_interval TIME(1) = NULL
	, @connections_acquired INT = 0 OUTPUT
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @nsql NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@variable_id INT = NULL
			, @current_modified_date DATETIME2(7) = NULL
			, @enabled_connections INT = NULL
			, @poll_interval_string VARCHAR(12) = NULL
-- ============

-- ============
/*<code_comment><![CDATA[initialize output parameters]]></code_comment>*/
SET @connections_acquired = 0
-- ============

-- ============
/*<code_comment><![CDATA[check parameters]]></code_comment>*/
IF NULLIF(@semaphore_name, N'') IS NULL
OR @connections_to_acquire IS NULL
OR @block_fl IS NULL
BEGIN
	RAISERROR('A required parameter is blank or null.', 16, 1)
	RETURN 1
END

IF @connections_to_acquire < 1
BEGIN
	RAISERROR('Must request at least 1 connection.', 16, 1)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[set variables]]></code_comment>*/
IF @requested_by_user IS NULL
	SET @requested_by_user = SUSER_SNAME()

IF @poll_interval IS NULL
OR @poll_interval < '00:00:00.1'
	SET @poll_interval = '00:00:10.0'
SET @poll_interval_string = CAST(@poll_interval AS VARCHAR(12))
-- ============

-- ============
/*<code_comment><![CDATA[fail if semaphore does not exist]]></code_comment>*/
IF ssf.gbl_exists_variable(@semaphore_name) = 0
BEGIN
	RAISERROR('The semaphore <%s> does not exist.', 16, 1, @semaphore_name)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[get semaphore attributes]]></code_comment>*/
SET @current_modified_date = ssf.gbl_get_modified_date(ssf.gbl_get_variable_id(@semaphore_name))

SET @enabled_connections = ssf.gbl_get_variable_value(@semaphore_name)

IF @enabled_connections IS NULL
BEGIN
	RAISERROR('The semaphore <%s> does not have a defined number of connections.', 16, 1, @semaphore_name)
	RETURN 1
END

IF @enabled_connections < @connections_to_acquire
AND @block_fl = 1
BEGIN
	RAISERROR('The semaphore <%s> is not enabled with at least <%d> connections.', 16, 1, @semaphore_name, @connections_to_acquire)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[begin loop: while connections remain to be acquired or blocking is requested]]></code_comment>*/
WHILE 1 = 1
BEGIN
	-- ============
	/*<code_comment><![CDATA[fail if semaphore does not have enough active connections]]></code_comment>*/
	IF ssf.gbl_get_semaphore_active_connection_count(@semaphore_name) < @connections_to_acquire
	BEGIN
		RAISERROR('The semaphore <%s> does not have at least <%d> active connections.', 16, 1, @semaphore_name, @connections_to_acquire)
		RETURN 1
	END
	-- ============
	
	-- ============
	/*<code_comment><![CDATA[acquire connections]]></code_comment>*/
	BEGIN TRY
		UPDATE TOP (@connections_to_acquire - @connections_acquired) ssf.gbl_semaphore_connection
		SET locked_by_spid = @@SPID
			, locked_by_user = @requested_by_user
			, modified_date = SYSDATETIME()
		WHERE semaphore_name = @semaphore_name
		AND locked_by_spid IS NULL
		
		SET @row_count = @@ROWCOUNT
		
		SET @connections_acquired += @row_count
	END TRY
	BEGIN CATCH
		SELECT @sql_error = ERROR_NUMBER()
			, @error_message = ERROR_MESSAGE()
		
		RAISERROR(@error_message, 16, 1)
		RETURN @sql_error
	END CATCH
	-- ============
	
	-- ============
	/*<code_comment><![CDATA[break if exit conditions are met]]></code_comment>*/
	IF @block_fl = 0
	OR @connections_acquired = @connections_to_acquire
		BREAK
	-- ============
	
	-- ============
	/*<code_comment><![CDATA[wait for poll interval]]></code_comment>*/
	WAITFOR DELAY @poll_interval_string
	-- ============
END
/*<code_comment><![CDATA[end loop: while connections remain to be acquired or blocking is requested]]></code_comment>*/
-- ============

RETURN @@ERROR
GO
